Recordset Object, Recordsets Collection Example

This example demonstrates Recordset objects and the Recordsets collection by opening four different types of Recordsets, enumerating the Recordsets collection of the current Database, and enumerating the Properties collection of each Recordset.

Sub RecordsetX()

   Dim dbsNorthwind As Database
   Dim rstTable As Recordset
   Dim rstDynaset As Recordset
   Dim rstSnapshot As Recordset
   Dim rstForwardOnly As Recordset
   Dim rstLoop As Recordset
   Dim prpLoop As Property

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")

   With dbsNorthwind

      ' Open one of each type of Recordset object.
      Set rstTable = .OpenRecordset("Categories", _
         dbOpenTable)
      Set rstDynaset = .OpenRecordset("Employees", _
         dbOpenDynaset)
      Set rstSnapshot = .OpenRecordset("Shippers", _
         dbOpenSnapshot)
      Set rstForwardOnly = .OpenRecordset _ 
         ("Employees", dbOpenForwardOnly)

      Debug.Print "Recordsets in Recordsets " & _
         "collection of dbsNorthwind"

      ' Enumerate Recordsets collection.
      For Each rstLoop In .Recordsets

         With rstLoop
            Debug.Print "  " & .Name

            ' Enumerate Properties collection of each
            ' Recordset object. Trap for any 
            ' properties whose values are invalid in 
            ' this context.
            For Each prpLoop In .Properties
               On Error Resume Next
               If prpLoop <> "" Then Debug.Print _
                  "    " & prpLoop.Name & _
                  " = " & prpLoop
               On Error GoTo 0
            Next prpLoop

         End With

      Next rstLoop

      rstTable.Close
      rstDynaset.Close
      rstSnapshot.Close
      rstForwardOnly.Close

      .Close
   End With

End Sub